--    Program: 		CAPARIMP_DISTRIBUTION_SET.sql
--    Description:  Import AR Distribution Set information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count_1		number;
   l_processed_count_2		number;
   l_rejected_count_1 		number;
   l_rejected_count_2 		number;
   l_err_msg_1				varchar2(2000);
   l_err_msg_2				varchar2(2000);
   l_err_msg_tmp_1			varchar2(2000); 
   l_err_msg_tmp_2			varchar2(2000);
   
   l_distribution_set_id    number;
   
   cursor cur_set is
      select * from CRST_AR_DISTRIBUTION_SETS_ALL
      for update;
      
   cursor cur_line(p_distribution_set_id number) is
      select * from CRST_AR_DISTRIBUTION_SET_LINES
      where distribution_set_id = p_distribution_set_id;   

begin
   l_processed_count_1 := 0;
   l_rejected_count_1  := 0;
   l_err_msg_1 := '';
   
   l_processed_count_2 := 0;
   l_rejected_count_2  := 0;
   l_err_msg_2 := '';
   
   for cur1 in cur_set loop   	    
   	 l_err_msg_tmp_1 := '';
   	 
   	 select AR_DISTRIBUTION_SETS_S.NEXTVAL
   	 into l_distribution_set_id
   	 from dual;
   	 
   	 update crst_ar_distribution_sets_all
   	 set    distribution_set_id = l_distribution_set_id
   	 where  distribution_set_id = cur1.DISTRIBUTION_SET_ID;
   	 
   	 begin
       -- import data
       insert into AR_DISTRIBUTION_SETS_ALL
       (
         DISTRIBUTION_SET_ID         ,
         DISTRIBUTION_SET_NAME       ,
         LAST_UPDATE_DATE            ,
         LAST_UPDATED_BY             ,
         LAST_UPDATE_LOGIN           ,
         CREATION_DATE               ,
         CREATED_BY                  ,
         STATUS                      ,
         DESCRIPTION                 ,
         TOTAL_PERCENT_DISTRIBUTION  ,
         ORG_ID 
       )
       values
       (
         l_distribution_set_id            ,
         cur1.DISTRIBUTION_SET_NAME       ,
         cur1.LAST_UPDATE_DATE            ,
         CAPBRIMP_MAPPING.GetUserMapping(cur1.LAST_UPDATED_BY),
         cur1.LAST_UPDATE_LOGIN           ,
         cur1.CREATION_DATE               ,
         CAPBRIMP_MAPPING.GetUserMapping(cur1.CREATED_BY),
         cur1.STATUS                      ,
         cur1.DESCRIPTION                 ,
         cur1.TOTAL_PERCENT_DISTRIBUTION  ,
         CAPBRIMP_MAPPING.GetOrgMapping(cur1.ORG_ID)
       );  
     exception
       when others then
     	 l_err_msg_tmp_1 := sqlerrm;
     end;
     
     for cur2 in cur_line(cur1.distribution_set_id) loop 
       l_err_msg_tmp_2 := '';
       	  	 
       begin	  	       
         -- import data
         insert into AR_DISTRIBUTION_SET_LINES_ALL
         (
           DISTRIBUTION_SET_ID       ,
           DIST_CODE_COMBINATION_ID  ,
           SET_OF_BOOKS_ID           ,
           LAST_UPDATE_DATE          ,
           LAST_UPDATED_BY           ,
           LAST_UPDATE_LOGIN         ,
           CREATION_DATE             ,
           CREATED_BY                ,
           PERCENT_DISTRIBUTION      ,
           DESCRIPTION               ,
           ORG_ID 
         )
         values
         (
           l_distribution_set_id          ,
           CAPBRIMP_MAPPING.GetCCIDMapping(cur2.DIST_CODE_COMBINATION_ID),
           CAPBRIMP_MAPPING.GetSOBIDMapping(cur2.SET_OF_BOOKS_ID),
           cur2.LAST_UPDATE_DATE          ,
           CAPBRIMP_MAPPING.GetUserMapping(cur2.LAST_UPDATED_BY),
           cur2.LAST_UPDATE_LOGIN         ,
           cur2.CREATION_DATE             ,
           CAPBRIMP_MAPPING.GetUserMapping(cur2.CREATED_BY),
           cur2.PERCENT_DISTRIBUTION      ,
           cur2.DESCRIPTION               ,
           CAPBRIMP_MAPPING.GetOrgMapping(cur2.ORG_ID)  
         );  
       exception
         when others then
     	   l_err_msg_tmp_2 := sqlerrm;
       end;
	   
	   if l_err_msg_tmp_2 = '' then
         l_processed_count_2 := l_processed_count_2 + 1;  
       else
         l_rejected_count_2 := l_rejected_count_2 + 1;
         l_err_msg_2 := l_err_msg_tmp_2; 
       end if;
     end loop;
     
     if l_err_msg_tmp_1 = '' then
       l_processed_count_1 := l_processed_count_1 + 1;  
     else
       l_rejected_count_1 := l_rejected_count_1 + 1;
       l_err_msg_1 := l_err_msg_tmp_1; 
     end if;
   end loop;
   
   if l_rejected_count_1 = 0 and l_rejected_count_2 = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_DISTRIBUTION_SET', sysdate, l_processed_count_1, l_rejected_count_1, l_processed_count_1-l_rejected_count_1, l_err_msg_1 );
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_DISTRIBUTION_SET_LINE', sysdate, l_processed_count_2, l_rejected_count_2, l_processed_count_2-l_rejected_count_2, l_err_msg_2 );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit